package cn.db101.dao;

import cn.db101.model.book_shelf;
import cn.db101.util.DbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class shelfDao {
	private DbUtil dbUtil;
	private Connection connection;
	public ArrayList<book_shelf> getAll(){
		ArrayList<book_shelf> reShelfs = new ArrayList<>();
		try {
			dbUtil = new DbUtil();
			connection = dbUtil.getCon();
			String sql ="select * from book_shelf"; //��
			PreparedStatement pre = connection.prepareStatement(sql);
			ResultSet rs=pre.executeQuery();
			
			while(rs.next()) {
				book_shelf b = new book_shelf();
				b.setLocation(rs.getString(2));
				b.setBooks_type(rs.getString(3));
				b.setBook_shelf_no(rs.getString(1));
				reShelfs.add(b);
			}
			rs.close();
			pre.close();
			dbUtil.closeCon(connection);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return reShelfs;
	}
	public String findLocation(String id) {
		try {
			dbUtil = new DbUtil();
			connection = dbUtil.getCon();
			String sql ="select * from book_shelf where book_shelf_no = ? "; //��
			
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setString(1, id);
			
			
			ResultSet rs=pre.executeQuery();
			
			while(rs.next()) {
				return rs.getString(2);
			}
			rs.close();
			pre.close();
			dbUtil.closeCon(connection);
		} catch (Exception e) {
			// TODO: handle exception
			return null;
		}
		return null;
	}
	public int updateInfo(String oldNa,String newNa) {
		//����ֵΪ 0����û�и��� 1������³ɹ� ����1��ʾ�������������ݴ��ڶ����������
		try {
			dbUtil = new DbUtil();
			connection = dbUtil.getCon();
			String sql ="update book_shelf set books_type = ? where books_type = ? "; //��
			
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setString(2, oldNa);
			pre.setString(1, newNa);
			int res=pre.executeUpdate();
			pre.close();
			dbUtil.closeCon(connection);
			return res;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return 0;
	}
	public int addInfo(String type,String location) {
		//����ֵΪ 0����û�в���ɹ� 1�������ɹ�
		try {
			dbUtil = new DbUtil();
			connection = dbUtil.getCon();
			String sql ="insert into book_shelf(location,books_type) values(?,?)"; //��
			
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setString(2, type);
			pre.setString(1, location);
			int res=pre.executeUpdate();
			pre.close();
			dbUtil.closeCon(connection);
			return res;
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return 0;
	}
	public int alterCatagory(String oldCatagory, String newCatagory) {
		try {
			dbUtil = new DbUtil();
			connection = dbUtil.getCon();
			String sql ="update book_shelf set books_type='" + newCatagory + "' where books_type='" + oldCatagory + "'"; //��
			
			PreparedStatement pre = connection.prepareStatement(sql);
			int result = pre.executeUpdate();
			pre.close();
			dbUtil.closeCon(connection);
			return result;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return 0;
		}
	}
	public int addCatagory(String newCatagory, String newLocation) {
		try {
			dbUtil = new DbUtil();
			connection = dbUtil.getCon();
			String sql1 = "select max(book_shelf_no) from book_shelf";
			PreparedStatement pre1 = connection.prepareStatement(sql1);
			ResultSet rs = pre1.executeQuery();
			int shelfid = 999999;
			if(rs.next()) {
				shelfid = Integer.parseInt(rs.getString(1))+1;
			}else {
				return 0;
			}
			
			String sql ="insert into book_shelf(book_shelf_no, location, books_type) values(?, ?, ?)"; //��
			
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setInt(1, shelfid);
			pre.setString(2, newLocation);
			pre.setString(3, newCatagory);
			int res = pre.executeUpdate();
			pre.close();
			dbUtil.closeCon(connection);
			return res;
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return 0;
		}
		
	}
	/*
	public static void main(String[] args) {
		new shelfDao().alterCatagory("��ѧ��", "������");
	}
	public static void main(String[] args) {
		new shelfDao().getAll();
	}*/

}

